--script to unload user defined types definition

declare ctype cursor for
select a.name, 
	(select name from systypes b where b.usertype=(select min(c.usertype) from systypes c where c.hierarchy=a.hierarchy)) dbname, 
	a.length, 
	a.prec, 
	a.scale, 
	a.ident,
	a.allownulls
from systypes a 
where a.usertype>100 
order by 1
go

create table #tmp(
	id	numeric(10) identity,
	txt varchar(255),
	primary key(id)
)
go

declare @name varchar(255)
declare @dbname varchar(255)
declare @length int
declare @prec int
declare @scale int
declare @ident int
declare @allownulls int
declare @null varchar(255)

open ctype
 
fetch ctype into @name,@dbname,@length,@prec,@scale,@ident,@allownulls
 
while (@@sqlstatus = 0) begin 
	insert into #tmp(txt) values("if exists(select 1 from dbo.systypes where name ='"+@name+"')"+char(10) )
	insert into #tmp(txt) values("	execute sp_droptype "+@name+char(10) )
	insert into #tmp(txt) values("go"+char(10))
	
	if @prec is null begin
		if @dbname not in('timestamp','tinyint','text','smallmoney','smallint','smalldatetime','real','moneyn','money','intn','int','image','floatn','float','decimaln','decimal','datetimn','datetime','bit')
			select @dbname=@dbname+'('+convert(varchar(10),@length)+')'
	end
	else begin
		select @dbname=@dbname+'('+convert(varchar(10),@prec)
		if @scale>0 select @dbname=@dbname+','+convert(varchar(10),@scale)
		select @dbname=@dbname+')'
	end
	if @ident=1 select @null='identity'
	else begin
		if @allownulls=1 select @null='null'
		else select @null='not null'
	end
	insert into #tmp(txt) values("execute sp_addtype "+@name+", '"+@dbname+"', '"+@null+"'"+char(10))
	insert into #tmp(txt) values("go"+char(10))
	insert into #tmp(txt) values(char(10))

	fetch ctype into @name,@dbname,@length,@prec,@scale,@ident,@allownulls
end 
 
close ctype 
deallocate cursor ctype
go
select txt from #tmp order by id
go
drop table #tmp
go
